package com.yellowpage.ndc.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;
import com.yellowpage.ndc.data.ManageRegion;
import com.yellowpage.ndc.dao.ManageRegionDao;

public class ManageRegionServiceImpl extends BaseServiceImpl implements ManageRegionService {
	  private ManageRegionDao manageRegionDao;
	
	  public void setManageRegionDao(ManageRegionDao manageRegionDao) {
		  this.manageRegionDao = manageRegionDao;
	  }
	
	
	  /* Smooth generate Method */ 
	  public ManageRegion getManageRegion(Long id){
	      return manageRegionDao.get(id);
	  }
	  
    public List<ManageRegion> getAllManageRegion(){
        return manageRegionDao.getAll();
    }
    
    public List<Map> queryManageRegionByPage(Map queryMap, PageRender pageRender) {		
        
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				"SELECT a.REGION_CODE , a.SHORT_NAME , a.ENG_NAME , a.TEL_LENGTH , a.AREA_CODE , a.ABBR_NAME , a.NOTE , a.NAME    FROM db_ndc.MANAGE_REGION a WHERE 1=1 ")
				    			.addCondition(" and REGION_CODE LIKE :regionCode ")
							    			.addCondition(" and SHORT_NAME LIKE :shortName ")
							    			.addCondition(" and ENG_NAME LIKE :engName ")
							    			.addCondition(" and TEL_LENGTH LIKE :telLength ")
							    			.addCondition(" and AREA_CODE LIKE :areaCode ")
							    			.addCondition(" and ABBR_NAME LIKE :abbrName ")
							    			.addCondition(" and NOTE LIEK :note ")
							    			.addCondition(" and NAME LIKE :name ")
							    ; 		    
		    

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}
public List<Map> ManageRegionexcel(Map queryMap) {		
        
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				"SELECT a.REGION_CODE , a.SHORT_NAME , a.ENG_NAME , a.TEL_LENGTH , a.AREA_CODE , a.ABBR_NAME , a.NOTE , a.NAME    FROM db_ndc.MANAGE_REGION a WHERE 1=1 ")
				    			.addCondition(" and REGION_CODE LIKE :regionCode ")
							    			.addCondition(" and SHORT_NAME LIKE :shortName ")
							    			.addCondition(" and ENG_NAME LIKE :engName ")
							    			.addCondition(" and TEL_LENGTH LIKE :telLength ")
							    			.addCondition(" and AREA_CODE LIKE :areaCode ")
							    			.addCondition(" and ABBR_NAME LIKE :abbrName ")
							    			.addCondition(" and NOTE LIKE :note ")
							    			.addCondition(" and NAME LIEK :name ")
							    ; 		
		    

		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
	} 
    
    public void saveManageRegion(ManageRegion manageRegion){
        manageRegionDao.save(manageRegion);
    }
    
	  public void updateManageRegion(ManageRegion manageRegion){
	      manageRegionDao.update(manageRegion);
	  }
	  
	  public void delete(List deleteList){
	      manageRegionDao.remove(deleteList);
	  }
	  
	  public String getCodeName(String code){
		  
		  QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select NAME from db_ndc.MANAGE_REGION where REGION_CODE ='"+code+"' "); 	
	        
			 List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());
			  if(resultList.size()>0){
				  return resultList.get(0).get("NAME").toString();
			  }else{
				  return "";
			  }
		 
	  }
	  
	  
	  public List<Map> getManageRegionTree(Map queryMap){
		  QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
					"SELECT a.REGION_CODE , a.SHORT_NAME , a.ENG_NAME , a.TEL_LENGTH , a.AREA_CODE , a.ABBR_NAME , a.NOTE , a.NAME    FROM db_ndc.MANAGE_REGION a WHERE 1=1 ")
					    			.addCondition(" and REGION_CODE LIKE :regionCode ")
								    			.addCondition(" and NAME LIEK :name ")
								    ; 		
			    

			List<Map> resultList = sqlAssistant.query(
					baseDao.getConnection(), queryMap);
			
			List<Map> list = new ArrayList<Map>();
			
			List<Map> provinceList = new ArrayList<Map>();

			List<Map> cityList = new ArrayList<Map>();
			
			List<Map> districtList = new ArrayList<Map>();
			
			
			
			
			for (Map map : resultList) {
				
				
				String province=map.get("REGION_CODE").toString().substring(2, 6);
				String city=map.get("REGION_CODE").toString().substring(4, 6);
				
				if(province.equals("0000")){
					Map regionMap = new HashMap();
					regionMap.put("provinceCode", map.get("REGION_CODE"));
					regionMap.put("provinceName", map.get("NAME"));
					provinceList.add(regionMap);
				}
				else if(city.equals("00")){
					Map cityMap = new HashMap();
					cityMap.put("cityCode", map.get("REGION_CODE"));
					cityMap.put("cityName", map.get("NAME"));
					cityList.add(cityMap);
				}else{
					Map districtMap = new HashMap();
					districtMap.put("districtCode", map.get("REGION_CODE"));
					districtMap.put("districtName", map.get("NAME"));
					districtList.add(districtMap);
				}

			}

			if(provinceList.size()>0){
			
				for (Map province : provinceList) {
					
					List<Map> cityAList = new ArrayList<Map>();
					
					
					Map provinceAMap = new HashMap();
					provinceAMap.put("code", province.get("provinceCode"));
					provinceAMap.put("name", province.get("provinceName"));	
					String provinceStr=province.get("provinceCode").toString().substring(0, 2);
					
					for (Map city : cityList) {
						List<Map> districtAList = new ArrayList<Map>();
						String cityStr=city.get("cityCode").toString().substring(0, 2);
						
						if(provinceStr.equals(cityStr)){
							Map cityAMap = new HashMap();
							cityAMap.put("code", city.get("cityCode"));
							cityAMap.put("name", city.get("cityName"));
							
							String cityStrF=city.get("cityCode").toString().substring(0, 4);
							
							
							for (Map district : districtList) {
								
								String districtStr=district.get("districtCode").toString().substring(0, 4);
								
								if(districtStr.equals(cityStrF)){
									Map districtAMap = new HashMap();
									districtAMap.put("code", district.get("districtCode"));
									districtAMap.put("name", district.get("districtName"));
									districtAList.add(districtAMap);
									
								}
								cityAMap.put("district", districtAList);
							}
							
							
							cityAList.add(cityAMap);
						}
						provinceAMap.put("city", cityAList);
						
						
					}
					list.add(provinceAMap);
					
				}
				return list;
			}
			else if(provinceList.size()==0 && cityList.size()>0){
				List<Map> cityAList = new ArrayList<Map>();
				for (Map city : cityList) {
					List<Map> districtAList = new ArrayList<Map>();
					Map cityAMap = new HashMap();
					cityAMap.put("code", city.get("cityCode"));
					cityAMap.put("name", city.get("cityName"));
					String cityStrF=city.get("cityCode").toString().substring(0, 4);
					
					for (Map district : districtList) {
						
						String districtStr=district.get("districtCode").toString().substring(0, 4);
						
						if(districtStr.equals(cityStrF)){
							Map districtAMap = new HashMap();
							districtAMap.put("code", district.get("districtCode"));
							districtAMap.put("name", district.get("districtName"));
							districtAList.add(districtAMap);
							
						}
						cityAMap.put("city", districtAList);
					}
					
					
					cityAList.add(cityAMap);
				}
				
				return cityAList;
			}
			else if(provinceList.size()==0 && cityList.size()==0 && districtList.size()>0){
				List<Map> districtAList = new ArrayList<Map>();
				for (Map district : districtList) {

						Map districtAMap = new HashMap();
						districtAMap.put("code", district.get("districtCode"));
						districtAMap.put("name", district.get("districtName"));
						districtAList.add(districtAMap);
				}
				
				return districtAList;
			}else{
				return list;
			}
			
			
		  
	  }
	 
	
}	